﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data;

using SF.Utilities;

namespace SF.Import
{
    public static class NPOIImport
    {
        /// <summary>
        /// 根据excel文件获取指定sheet页的excel数据并转换为指定类型的集合
        /// </summary>
        /// <typeparam name="T">目标类型</typeparam>
        /// <param name="excelFilePath">excel文件地址</param>
        /// <param name="specialSheetIndex">要获取的指定sheet页索引</param>
        /// <returns></returns>
        public static IEnumerable<T> GetExcelData<T>(string excelFilePath, UInt32 specialSheetIndex) where T : class,new()
        {
            if (string.IsNullOrWhiteSpace(excelFilePath)) { throw new ArgumentNullException("excelFilePath", "参数不能为空。"); }
            FileInfo file = new FileInfo(excelFilePath);
            return GetExcelData<T>(file, specialSheetIndex);
        }

        /// <summary>
        /// 根据excel文件获取指定sheet页的excel数据并转换为指定类型的集合
        /// </summary>
        /// <typeparam name="T">目标类型</typeparam>
        /// <param name="excelFile">excel文件对象</param>
        /// <param name="specialSheetIndex">要获取的指定sheet页索引</param>
        /// <returns></returns>
        public static IEnumerable<T> GetExcelData<T>(FileInfo excelFile, UInt32 specialSheetIndex) where T : class,new()
        {
            List<Dictionary<string, object>> sheetData = GetExcelData(excelFile, specialSheetIndex);

            return sheetData == null ? null : sheetData.Select(s => s.ToModel<T>());
        }

        /// <summary>
        /// 根据excel文件获取完整的excel数据并转换为指定类型的集合
        /// </summary>
        /// <typeparam name="T">目标类型</typeparam>
        /// <param name="excelFilePath">excel文件地址</param>
        /// <param name="forceTitleWithString">强制标题为字符串</param>
        /// <returns></returns>
        public static Dictionary<string, IEnumerable<T>> GetExcelData<T>(string excelFilePath, bool forceTitleWithString) where T : class,new()
        {
            if (string.IsNullOrWhiteSpace(excelFilePath)) { throw new ArgumentNullException("excelFilePath", "参数不能为空。"); }
            FileInfo file = new FileInfo(excelFilePath);
            return GetExcelData<T>(file, forceTitleWithString);
        }

        /// <summary>
        /// 根据excel文件获取完整的excel数据并转换为指定类型的集合
        /// </summary>
        /// <typeparam name="T">目标类型</typeparam>
        /// <param name="excelFile">excel文件对象</param>
        /// <param name="forceTitleWithString">强制标题为字符串</param>
        /// <returns></returns>
        public static Dictionary<string, IEnumerable<T>> GetExcelData<T>(FileInfo excelFile, bool forceTitleWithString) where T : class,new()
        {
            Dictionary<string, List<Dictionary<string, object>>> sheetMaps = GetExcelData(excelFile, forceTitleWithString);
            Dictionary<string, IEnumerable<T>> result = null;

            foreach (var item in sheetMaps)
            {
                IEnumerable<T> data = item.Value.Select(s => s.ToModel<T>());
                result.Add(item.Key, data);
            }

            return result;
        }

        /// <summary>
        /// 根据excel文件获取指定sheet页的excel数据
        /// </summary>
        /// <param name="excelFilePath">excel文件地址</param>
        /// <param name="specialSheetIndex">要获取的指定sheet页索引</param>
        /// <returns></returns>
        public static List<Dictionary<string, object>> GetExcelData(string excelFilePath, UInt32 specialSheetIndex)
        {
            if (string.IsNullOrWhiteSpace(excelFilePath)) { throw new ArgumentNullException("excelFilePath", "参数不能为空。"); }
            FileInfo file = new FileInfo(excelFilePath);
            return GetExcelData(file, specialSheetIndex);
        }

        /// <summary>
        /// 根据excel文件获取指定sheet页的excel数据
        /// </summary>
        /// <param name="excelFile">excel文件对象</param>
        /// <param name="specialSheetIndex">要获取的指定sheet页索引</param>
        /// <returns></returns>
        public static List<Dictionary<string, object>> GetExcelData(FileInfo excelFile, UInt32 specialSheetIndex)
        {
            if (excelFile == null) { throw new ArgumentNullException("excelFile", "参数不能为空。"); }

            string extName = excelFile.Extension;
            if (string.IsNullOrWhiteSpace(extName) || !(new string[] { "xls", "xlsx" }).Contains(extName))
            {
                throw new ArgumentException("文件格式不符合excel标准。");
            }

            FileStream fs = excelFile.OpenRead();
            NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
            int count = excel.NumberOfSheets;
            if (specialSheetIndex >= count) { throw new Exception("指定的sheet页索引超出界限。"); }

            List<Dictionary<string, object>> rowDatas = null;
            for (int index = 0; index < count; index++)
            {
                if (specialSheetIndex != index) { continue; }

                NPOI.SS.UserModel.ISheet sheet = excel.GetSheetAt(index);
                int rowsLength = sheet.LastRowNum;
                if (rowsLength == 0) { continue; }

                int titleLength = sheet.GetRow(0).LastCellNum;
                string[] titles = new string[titleLength];

                for (int rowIndex = 0; rowIndex <= rowsLength; rowIndex++)
                {
                    NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex);
                    if (row == null) { continue; }

                    int columnsLength = titleLength;
                    //key为首行列名，value为列值
                    Dictionary<string, object> rowData = new Dictionary<string, object>();
                    for (int columnIndex = 0; columnIndex < columnsLength; columnIndex++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(columnIndex);
                        if (cell == null)
                        {
                            if (rowIndex == 0) { continue; }
                            else
                            {
                                cell = row.CreateCell(columnIndex, NPOI.SS.UserModel.CellType.String);
                                cell.SetCellValue("");
                            }
                        }

                        if (rowIndex == 0)
                        {
                            NPOI.SS.UserModel.CellType cellType = cell.CellType;
                            if (cellType != NPOI.SS.UserModel.CellType.String)
                            {
                                throw new Exception("excel中列标题[" + cell.ToString() + "]的数据格式不是字符，无法处理。");
                            }
                            string value = cell.StringCellValue;
                            //首行，存储列名
                            if (titles.Any(a => a.Equals(value)))
                            {
                                titles[columnIndex] = value + "(" + (columnIndex + 1) + ")";
                            }
                            else
                            {
                                titles[columnIndex] = value;
                            }
                        }
                        else
                        {
                            object value;
                            switch (cell.CellType)
                            {
                                case NPOI.SS.UserModel.CellType.Boolean:
                                    value = cell.BooleanCellValue;
                                    break;
                                case NPOI.SS.UserModel.CellType.Numeric:
                                    if (NPOI.HSSF.UserModel.HSSFDateUtil.IsCellDateFormatted(cell) || NPOI.HSSF.UserModel.HSSFDateUtil.IsCellInternalDateFormatted(cell))
                                    {
                                        value = cell.DateCellValue;
                                    }
                                    else
                                    {
                                        value = cell.NumericCellValue;
                                    }
                                    break;
                                case NPOI.SS.UserModel.CellType.Formula:
                                    value = cell.CellFormula;
                                    break;
                                case NPOI.SS.UserModel.CellType.String:
                                    value = cell.StringCellValue;
                                    break;
                                default:
                                    value = ""; break;
                            }
                            rowData.TryAdd(titles[columnIndex], value);
                        }
                    }
                    if (rowDatas == null) { rowDatas = new List<Dictionary<string, object>>(); }
                    if (rowData.Count > 0) { rowDatas.Add(rowData); }
                }
            }

            return rowDatas;
        }

        /// <summary>
        /// 根据excel文件获取完整的excel数据
        /// </summary>
        /// <param name="excelFilePath">excel文件地址</param>
        /// <param name="forceTitleWithString">强制标题为字符串</param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, object>>> GetExcelData(string excelFilePath, bool forceTitleWithString)
        {
            if (string.IsNullOrWhiteSpace(excelFilePath)) { throw new ArgumentNullException("excelFilePath", "参数不能为空。"); }
            FileInfo file = new FileInfo(excelFilePath);
            return GetExcelData(file, forceTitleWithString);
        }

        /// <summary>
        /// 根据excel文件获取完整的excel数据
        /// </summary>
        /// <param name="excelFile">excel文件对象</param>
        /// <param name="forceTitleWithString">强制标题为字符串</param>
        /// <returns></returns>
        public static Dictionary<string, List<Dictionary<string, object>>> GetExcelData(FileInfo excelFile, bool forceTitleWithString)
        {
            if (excelFile == null) { throw new ArgumentNullException("excelFile", "参数不能为空。"); }

            string extName = excelFile.Extension;
            if (string.IsNullOrWhiteSpace(extName) || !(new string[] { ".xls", ".xlsx" }).Contains(extName))
            {
                throw new ArgumentException("当前文件格式[" + extName + "]不符合excel标准。");
            }

            FileStream fs = excelFile.OpenRead();
            NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
            int count = excel.NumberOfSheets;
            //key为sheet名，value为行集合
            Dictionary<string, List<Dictionary<string, object>>> sheetMaps = null;
            for (int index = 0; index < count; index++)
            {
                NPOI.SS.UserModel.ISheet sheet = excel.GetSheetAt(index);
                int rowsLength = sheet.LastRowNum;
                if (rowsLength == 0) { continue; }

                List<Dictionary<string, object>> rowDatas = new List<Dictionary<string, object>>();
                int titleLength = sheet.GetRow(0).LastCellNum;
                string[] titles = new string[titleLength];

                for (int rowIndex = 0; rowIndex <= rowsLength; rowIndex++)
                {
                    NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex);
                    if (row == null) { continue; }

                    int columnsLength = titleLength;
                    //key为首行列名，value为列值
                    Dictionary<string, object> rowData = new Dictionary<string, object>();
                    for (int columnIndex = 0; columnIndex < columnsLength; columnIndex++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(columnIndex);
                        if (cell == null)
                        {
                            if (rowIndex == 0) { continue; }
                            else
                            {
                                cell = row.CreateCell(columnIndex, NPOI.SS.UserModel.CellType.String);
                                cell.SetCellValue("");
                            }
                        }

                        if (rowIndex == 0)
                        {
                            NPOI.SS.UserModel.CellType cellType = cell.CellType;
                            if ((!forceTitleWithString && cellType != NPOI.SS.UserModel.CellType.String))
                            {
                                throw new Exception("excel中列标题[" + cell.ToString() + "]的数据格式不是字符，无法处理。");
                            }

                            string value = null;
                            switch (cellType)
                            {
                                case NPOI.SS.UserModel.CellType.String:
                                    value = cell.StringCellValue;
                                    break;
                                case NPOI.SS.UserModel.CellType.Numeric:
                                    value = cell.NumericCellValue.ToString();
                                    break;
                                case NPOI.SS.UserModel.CellType.Boolean:
                                    value = cell.BooleanCellValue.ToString();
                                    break;
                                case NPOI.SS.UserModel.CellType.Formula:
                                    value = cell.CellFormula;
                                    break;
                            }
                            //首行，存储列名
                            if (titles.Any(a => a != null && a.Equals(value)))
                            {
                                titles[columnIndex] = value + "(" + (columnIndex + 1) + ")";
                            }
                            else
                            {
                                titles[columnIndex] = value;
                            }
                        }
                        else
                        {
                            object value;
                            switch (cell.CellType)
                            {
                                case NPOI.SS.UserModel.CellType.Boolean:
                                    value = cell.BooleanCellValue;
                                    break;
                                case NPOI.SS.UserModel.CellType.Numeric:
                                    if (NPOI.HSSF.UserModel.HSSFDateUtil.IsCellDateFormatted(cell) || NPOI.HSSF.UserModel.HSSFDateUtil.IsCellInternalDateFormatted(cell))
                                    {
                                        value = cell.DateCellValue;
                                    }
                                    else
                                    {
                                        value = cell.NumericCellValue;
                                    }
                                    break;
                                case NPOI.SS.UserModel.CellType.Formula:
                                    value = cell.CellFormula;
                                    break;
                                case NPOI.SS.UserModel.CellType.String:
                                    value = cell.StringCellValue;
                                    break;
                                default:
                                    value = ""; break;
                            }
                            rowData.TryAdd(titles[columnIndex], value);
                        }
                    }
                    if (rowData.Count > 0) { rowDatas.Add(rowData); }
                }
                if (sheetMaps == null) { sheetMaps = new Dictionary<string, List<Dictionary<string, object>>>(); }
                sheetMaps.Add(sheet.SheetName, rowDatas);
            }

            return sheetMaps;
        }

        /// <summary>
        /// 根据excel文件获取指定sheet页的excel数据
        /// </summary>
        /// <param name="excelFilePath">excel文件地址</param>
        /// <param name="specialSheetIndex">要获取的指定sheet页索引</param>
        /// <returns></returns>
        public static DataTable GetExcelDataToDataTable(string excelFilePath, UInt32 specialSheetIndex)
        {
            if (string.IsNullOrWhiteSpace(excelFilePath)) { throw new ArgumentNullException("excelFilePath", "参数不能为空。"); }
            FileInfo file = new FileInfo(excelFilePath);
            return GetExcelDataToDataTable(file, specialSheetIndex);
        }

        /// <summary>
        /// 根据excel文件获取指定sheet页的excel数据
        /// </summary>
        /// <param name="excelFile">excel文件对象</param>
        /// <param name="specialSheetIndex">要获取的指定sheet页索引</param>
        /// <returns></returns>
        public static DataTable GetExcelDataToDataTable(FileInfo excelFile, UInt32 specialSheetIndex)
        {
            if (excelFile == null) { throw new ArgumentNullException("excelFile", "参数不能为空。"); }

            string extName = excelFile.Extension;
            if (string.IsNullOrWhiteSpace(extName) || !(new string[] { ".xls", ".xlsx" }).Contains(extName))
            {
                throw new ArgumentException("当前文件格式[" + extName + "]不符合excel标准。");
            }

            FileStream fs = excelFile.OpenRead();
            NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
            int count = excel.NumberOfSheets;
            if (specialSheetIndex >= count) { throw new Exception("指定的sheet页索引超出界限。"); }

            DataTable rowDatas = null;
            for (int index = 0; index < count; index++)
            {
                if (specialSheetIndex != index) { continue; }

                NPOI.SS.UserModel.ISheet sheet = excel.GetSheetAt(index);
                int rowsLength = sheet.LastRowNum;
                if (rowsLength == 0) { continue; }

                rowDatas = new DataTable(sheet.SheetName);
                //计算最大的列数
                int titleLength = 0;
                for (int rowIndex = 0; rowIndex <= rowsLength; rowIndex++)
                {
                    titleLength = Math.Max(titleLength, sheet.GetRow(rowIndex).LastCellNum);
                }
                //计算各列名
                for (UInt32 i = 0; i < titleLength; i++)
                {
                    string columnName = NPOIImport.GetExcelColumnOriginalNames(i + 1);
                    //构建datatable结构
                    rowDatas.Columns.Add(new DataColumn(columnName));
                }
                for (int rowIndex = 0; rowIndex <= rowsLength; rowIndex++)
                {
                    NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex);

                    if (row == null)
                    {
                        //空数据
                        rowDatas.Rows.Add(rowDatas.NewRow());
                        continue;
                    }

                    int columnsLength = sheet.GetRow(rowIndex).LastCellNum;
                    DataRow rowData = rowDatas.NewRow();
                    for (int columnIndex = 0; columnIndex < columnsLength; columnIndex++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(columnIndex);
                        if (cell == null)
                        {
                            rowData[columnIndex] = null;
                            continue;
                        }

                        NPOI.SS.UserModel.CellType cellType = cell.CellType;
                        object cellValue;
                        switch (cell.CellType)
                        {
                            case NPOI.SS.UserModel.CellType.Boolean:
                                cellValue = cell.BooleanCellValue;
                                break;
                            case NPOI.SS.UserModel.CellType.Numeric:
                                if (NPOI.HSSF.UserModel.HSSFDateUtil.IsCellDateFormatted(cell) || NPOI.HSSF.UserModel.HSSFDateUtil.IsCellInternalDateFormatted(cell))
                                {
                                    cellValue = cell.DateCellValue;
                                }
                                else
                                {
                                    cellValue = cell.NumericCellValue;
                                }
                                break;
                            case NPOI.SS.UserModel.CellType.Formula:
                                cellValue = cell.CellFormula;
                                break;
                            case NPOI.SS.UserModel.CellType.String:
                                cellValue = cell.StringCellValue;
                                break;
                            case NPOI.SS.UserModel.CellType.Blank:
                                cellValue = "";
                                break;
                            case NPOI.SS.UserModel.CellType.Error:
                                cellValue = cell.ErrorCellValue;
                                break;
                            default:
                                cellValue = "[Unknown]";
                                break;
                        }
                        rowData[columnIndex] = cellValue;
                    }
                    rowDatas.Rows.Add(rowData);
                }
                break;
            }

            return rowDatas;
        }

        /// <summary>
        /// 根据excel文件获取完整的excel数据
        /// </summary>
        /// <param name="excelFilePath">excel文件地址</param>
        /// <returns></returns>
        public static DataSet GetExcelDataToDataSet(string excelFilePath)
        {
            if (string.IsNullOrWhiteSpace(excelFilePath)) { throw new ArgumentNullException("excelFilePath", "参数不能为空。"); }
            FileInfo file = new FileInfo(excelFilePath);
            return GetExcelDataToDataSet(file);
        }

        /// <summary>
        /// 根据excel文件获取完整的excel数据
        /// </summary>
        /// <param name="excelFile">excel文件对象</param>
        /// <returns></returns>
        public static DataSet GetExcelDataToDataSet(FileInfo excelFile)
        {
            if (excelFile == null) { throw new ArgumentNullException("excelFile", "参数不能为空。"); }

            string extName = excelFile.Extension;
            if (string.IsNullOrWhiteSpace(extName) || !(new string[] { ".xls", ".xlsx" }).Contains(extName))
            {
                throw new ArgumentException("当前文件格式[" + extName + "]不符合excel标准。");
            }

            FileStream fs = excelFile.OpenRead();
            NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
            int count = excel.NumberOfSheets;
            //key为sheet名，value为行集合
            DataSet sheetMaps = null;
            for (int index = 0; index < count; index++)
            {
                NPOI.SS.UserModel.ISheet sheet = excel.GetSheetAt(index);
                int rowsLength = sheet.LastRowNum;
                if (rowsLength == 0) { continue; }

                DataTable rowDatas = new DataTable(sheet.SheetName);
                //计算最大的列数
                int titleLength = 0;
                for (int rowIndex = 0; rowIndex <= rowsLength; rowIndex++)
                {
                    titleLength = Math.Max(titleLength, sheet.GetRow(rowIndex).LastCellNum);
                }
                //计算各列名
                for (UInt32 i = 0; i < titleLength; i++)
                {
                    string columnName = NPOIImport.GetExcelColumnOriginalNames(i + 1);
                    //构建datatable结构
                    rowDatas.Columns.Add(new DataColumn(columnName));
                }
                for (int rowIndex = 0; rowIndex <= rowsLength; rowIndex++)
                {
                    NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex);

                    if (row == null)
                    {
                        //空数据
                        rowDatas.Rows.Add(rowDatas.NewRow());
                        continue;
                    }

                    int columnsLength = sheet.GetRow(rowIndex).LastCellNum;
                    DataRow rowData = rowDatas.NewRow();
                    for (int columnIndex = 0; columnIndex < columnsLength; columnIndex++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(columnIndex);
                        if (cell == null)
                        {
                            rowData[columnIndex] = null;
                            continue;
                        }

                        NPOI.SS.UserModel.CellType cellType = cell.CellType;
                        object cellValue;
                        switch (cell.CellType)
                        {
                            case NPOI.SS.UserModel.CellType.Boolean:
                                cellValue = cell.BooleanCellValue;
                                break;
                            case NPOI.SS.UserModel.CellType.Numeric:
                                if (NPOI.HSSF.UserModel.HSSFDateUtil.IsCellDateFormatted(cell) || NPOI.HSSF.UserModel.HSSFDateUtil.IsCellInternalDateFormatted(cell))
                                {
                                    cellValue = cell.DateCellValue;
                                }
                                else
                                {
                                    cellValue = cell.NumericCellValue;
                                }
                                break;
                            case NPOI.SS.UserModel.CellType.Formula:
                                cellValue = cell.CellFormula;
                                break;
                            case NPOI.SS.UserModel.CellType.String:
                                cellValue = cell.StringCellValue;
                                break;
                            case NPOI.SS.UserModel.CellType.Blank:
                                cellValue = "";
                                break;
                            case NPOI.SS.UserModel.CellType.Error:
                                cellValue = cell.ErrorCellValue;
                                break;
                            default:
                                cellValue = "[Unknown]";
                                break;
                        }
                        rowData[columnIndex] = cellValue;
                    }
                    rowDatas.Rows.Add(rowData);
                }
                if (sheetMaps == null) { sheetMaps = new DataSet(); }
                sheetMaps.Tables.Add(rowDatas);
            }

            return sheetMaps;
        }

        /// <summary>
        /// 根据列序号获取excel原始列名
        /// </summary>
        /// <param name="count">列序号，从1开始</param>
        /// <returns></returns>
        public static string GetExcelColumnOriginalNames(UInt32 count)
        {
            count = count - 1;
            if (count >= 256)
            {
                throw new Exception("列数不能超过255行。");
            }

            if (count < 26)
            {
                return ((char)('A' + count)).ToString();
            }
            else
            {
                return ((char)('A' + ((count / 26) - 1))).ToString() + ((char)('A' + (count % 26))).ToString();
            }
        }

        /// <summary>
        /// 根据excel文件获取完整的excel数据
        /// </summary>
        /// <param name="excelFile">excel文件对象</param>
        /// <returns></returns>
        public static DataSet test1(FileInfo excelFile)
        {
            if (excelFile == null) { throw new ArgumentNullException("excelFile", "参数不能为空。"); }

            string extName = excelFile.Extension;
            if (string.IsNullOrWhiteSpace(extName) || !(new string[] { ".xls", ".xlsx" }).Contains(extName))
            {
                throw new ArgumentException("当前文件格式[" + extName + "]不符合excel标准。");
            }

            FileStream fs = excelFile.OpenRead();
            //NPOI.HSSF.UserModel.HSSFWorkbook excel0 = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
            NPOI.SS.UserModel.IWorkbook excel = null;
            if (extName.ToLower() == ".xls")
            {
                excel = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
            }
            else
            {
                excel = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
            }
            int count = excel.NumberOfSheets;
            //key为sheet名，value为行集合
            DataSet sheetMaps = null;
            for (int index = 0; index < count; index++)
            {
                NPOI.SS.UserModel.ISheet sheet = excel.GetSheetAt(index);
                int rowsLength = sheet.LastRowNum;
                if (rowsLength == 0) { continue; }

                DataTable rowDatas = new DataTable(sheet.SheetName);
                //计算最大的列数
                int titleLength = 0;
                for (int rowIndex = 0; rowIndex <= rowsLength; rowIndex++)
                {
                    titleLength = Math.Max(titleLength, sheet.GetRow(rowIndex).LastCellNum);
                }
                //计算各列名
                for (UInt32 i = 0; i < titleLength; i++)
                {
                    string columnName = NPOIImport.GetExcelColumnOriginalNames(i + 1);
                    //构建datatable结构
                    rowDatas.Columns.Add(new DataColumn(columnName));
                }
                for (int rowIndex = 0; rowIndex <= rowsLength; rowIndex++)
                {
                    NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex);

                    if (row == null)
                    {
                        //空数据
                        rowDatas.Rows.Add(rowDatas.NewRow());
                        continue;
                    }

                    int columnsLength = sheet.GetRow(rowIndex).LastCellNum;
                    DataRow rowData = rowDatas.NewRow();
                    for (int columnIndex = 0; columnIndex < columnsLength; columnIndex++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(columnIndex);
                        if (cell == null)
                        {
                            rowData[columnIndex] = null;
                            continue;
                        }

                        NPOI.SS.UserModel.CellType cellType = cell.CellType;
                        object cellValue;
                        switch (cell.CellType)
                        {
                            case NPOI.SS.UserModel.CellType.Boolean:
                                cellValue = cell.BooleanCellValue;
                                break;
                            case NPOI.SS.UserModel.CellType.Numeric:
                                if (NPOI.HSSF.UserModel.HSSFDateUtil.IsCellDateFormatted(cell) || NPOI.HSSF.UserModel.HSSFDateUtil.IsCellInternalDateFormatted(cell))
                                {
                                    cellValue = cell.DateCellValue;
                                }
                                else
                                {
                                    cellValue = cell.NumericCellValue;
                                }
                                break;
                            case NPOI.SS.UserModel.CellType.Formula:
                                cellValue = cell.CellFormula;
                                break;
                            case NPOI.SS.UserModel.CellType.String:
                                cellValue = cell.StringCellValue;
                                break;
                            case NPOI.SS.UserModel.CellType.Blank:
                                cellValue = "";
                                break;
                            case NPOI.SS.UserModel.CellType.Error:
                                cellValue = cell.ErrorCellValue;
                                break;
                            default:
                                cellValue = "[Unknown]";
                                break;
                        }
                        rowData[columnIndex] = cellValue;
                    }
                    rowDatas.Rows.Add(rowData);
                }
                if (sheetMaps == null) { sheetMaps = new DataSet(); }
                sheetMaps.Tables.Add(rowDatas);
            }

            return sheetMaps;
        }
    }
}
